﻿using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml;
using Newtonsoft.Json;

namespace SqlMassDumper
{
    public partial class MainForm : Form
    {
        private SqlConnection _sqlConnection;
        private CancellationTokenSource _cancellationTokenSource;
        private SynchronizationContext _synchronizationContext;
 
        public MainForm()
        {
            InitializeComponent();
            ResetForm();
        }

        private void MainFormLoad(object sender, EventArgs e)
        {

        }

        private void ResetForm()
        {
            txtServerName.Text = string.Empty;
            txtUID.Text = string.Empty;
            txtPwd.Text = string.Empty;
            chkUseWindowsAuthentication.Checked = false;
            txtDumpPath.Text = string.Empty;
            txtLogPath.Text = string.Empty;
            _cancellationTokenSource = new CancellationTokenSource();
            _synchronizationContext = SynchronizationContext.Current;
            txtServerName.Focus();
        }

        private void BtnDumpXmlClick(object sender, EventArgs e)
        {
            if (!VerifyForm())
                return;

            StartDumping();

        }

        private void BtnDumpJsonClick(object sender, EventArgs e)
        {
            if (!VerifyForm())
                return;

            StartDumping(false);
        }

        private bool VerifyForm()
        {
            if (txtServerName.Text == string.Empty)
                return false;

            if ((txtUID.Text == string.Empty || txtPwd.Text == string.Empty) && !chkUseWindowsAuthentication.Checked)
                return false;

            string uIdAndPass;
            if (chkUseWindowsAuthentication.Checked)
                uIdAndPass = "Integrated Security=true;";
            else
                uIdAndPass = "UID=" + txtUID.Text + "; Pwd=" + txtPwd.Text + ";";

            // try to open the connection
            _sqlConnection = new SqlConnection
                                 {
                                     ConnectionString =
                                         "Persist Security Info=True;data source=" + txtServerName.Text + "; initial catalog=master; " +
                                         uIdAndPass + " MultipleActiveResultSets=True;"
                                 };

            try
            {
                _sqlConnection.Open();
            }
            catch (SqlException ex)
            {
                MessageBox.Show("Could not connect to server. Error: " + ex.Message);
                return false;
            }

            // try if directory exits and have permission to write
            try
            {
                if (!Directory.Exists(txtDumpPath.Text))
                    throw new IOException("Cannnot find path for dumping");

                if (!Directory.Exists(txtLogPath.Text))
                    throw new IOException("Cannnot find path for logging");

                // try to create file and delte 'em
                File.AppendAllText(Path.Combine(txtDumpPath.Text, "testFile.dump"), "Testing Dump");
                File.Delete(Path.Combine(txtDumpPath.Text, "testFile.dump"));

                File.AppendAllText(Path.Combine(txtLogPath.Text, "testFile.log"), "Testing Dump");
                File.Delete(Path.Combine(txtLogPath.Text, "testFile.log"));

            }
            catch (IOException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
            return true;
        }

        private void ChkUseWindowsAuthenticationCheckedChanged(object sender, EventArgs e)
        {
            txtUID.Enabled = txtPwd.Enabled = !chkUseWindowsAuthentication.Checked;
        }

        private void TxtDumpPathMouseDown(object sender, MouseEventArgs e)
        {
            using (var folderBrowseDialog = new FolderBrowserDialog())
            {
                if (folderBrowseDialog.ShowDialog() == DialogResult.OK)
                    txtDumpPath.Text = folderBrowseDialog.SelectedPath;
            }
        }

        private void TxtLogPathMouseDown(object sender, MouseEventArgs e)
        {
            using (var folderBrowseDialog = new FolderBrowserDialog())
            {
                if (folderBrowseDialog.ShowDialog() == DialogResult.OK)
                    txtLogPath.Text = folderBrowseDialog.SelectedPath;
            }
        }

        private void StartDumping(bool isXml = true, bool colsAsElements = true)
        {

            var sqlCommand = _sqlConnection.CreateCommand();
            var concurrentBag = new ConcurrentBag<string>();

            // get all databases
            sqlCommand.CommandText =
                "SELECT [name] FROM sys.databases where [name] NOT IN ('master', 'tempdb', 'model', 'msdb')";
            // can also add count(*) over(order by name) 'current', count(*) over(order by (select null)) 'count' 
            var dumpPath = txtDumpPath.Text;
            var logPath = txtLogPath.Text;

            toolStripStatusLabel1.Text = "Processing...";

            var reader = sqlCommand.ExecuteReader();
            var taskList = new List<Task>();
            while (reader.Read())
            {
                var localConnection = new SqlConnection(_sqlConnection.ConnectionString);
                //MessageBox.Show(localConnection.ConnectionString);
                var name = reader["name"].ToString();

                if (localConnection.State == ConnectionState.Closed)
                    localConnection.Open();
                localConnection.ChangeDatabase(name);

                var task = Task.Run(() =>
                {
                    try
                    {
                        _cancellationTokenSource.Token.ThrowIfCancellationRequested();
                        var sqlCmd = new ThreadLocal<SqlCommand>(localConnection.CreateCommand)
                        {
                            Value =
                            {
                                CommandText =
                                    "SELECT '[' + s.name + '].[' + t.name + ']' AS TableName, s.name, t.name FROM sys.tables t" +
                                    " INNER JOIN sys.schemas s on t.schema_id = s.schema_id"
                            }
                        };

                        var dataSet = new DataSet();
                        var dataAdapter = new ThreadLocal<SqlDataAdapter>(() => new SqlDataAdapter(sqlCmd.Value));
                        dataAdapter.Value.Fill(dataSet);

                        if (dataSet.Tables.Count != 0 && dataSet.Tables[0].Rows.Count != 0)
                        {

                            Parallel.For(0, dataSet.Tables[0].Rows.Count, new ParallelOptions { CancellationToken = _cancellationTokenSource.Token }, k =>
                            {

                                _cancellationTokenSource.Token.ThrowIfCancellationRequested();
                                var schema = new ThreadLocal<string>(() => dataSet.Tables[0].Rows[k][1].ToString());
                                var table = new ThreadLocal<string>(() => dataSet.Tables[0].Rows[k][2].ToString());
                                var fullTable = new ThreadLocal<string>(() => dataSet.Tables[0].Rows[k]["TableName"].ToString());
                                try
                                {
                                    var cmdTable = new ThreadLocal<SqlCommand>(localConnection.CreateCommand)
                                                        {
                                                            Value =
                                                                {
                                                                    CommandText = "SELECT * FROM " + fullTable.Value + " FOR XML AUTO, ELEMENTS, ROOT('" + schema.Value + "_" + table.Value + "')"
                                                                }
                                                        };
                                    var readerXml = new ThreadLocal<XmlReader>(() => cmdTable.Value.ExecuteXmlReader());
                                    while (readerXml.Value.Read())
                                    {
                                        var path = new ThreadLocal<string>(() => Path.Combine(dumpPath, name));
                                        Directory.CreateDirectory(Path.GetDirectoryName(path.Value + "\\" + schema.Value + ".xml"));

                                        if (isXml)
                                        {
                                            var xml = readerXml.Value.ReadOuterXml();
                                            File.WriteAllText(path.Value + "\\" + schema.Value + "_" + table.Value + ".xml", xml);
                                        }
                                        else
                                        {
                                            var xmlForJson = new XmlDocument();
                                            xmlForJson.LoadXml(readerXml.Value.ReadOuterXml());
                                            var json = JsonConvert.SerializeXmlNode(xmlForJson);
                                            File.WriteAllText(path.Value + "\\" + schema.Value + "_" + table.Value + ".json", json);
                                        }
                                    }
                                    readerXml.Value.Close();
                                }
                                catch (OperationCanceledException)
                                {
                                    if (toolStripStatusLabel1.Text == "Cancelled") return;
                                    MessageBox.Show("Operation Cancelled", "Cancelled", MessageBoxButtons.OK);
                                    toolStripStatusLabel1.Text = "Cancelled";
                                    _synchronizationContext.Post(_ => ResetForm(), null);
                                }
                                catch (Exception ex)
                                {
                                    concurrentBag.Add("Error Occured when dumping " + fullTable + " " + ex.Message);
                                }
                            });

                        }
                    }
                    catch (OperationCanceledException)
                    {
                        if (toolStripStatusLabel1.Text == "Cancelled") return;
                        MessageBox.Show("Operation Cancelled", "Cancelled", MessageBoxButtons.OK);
                        toolStripStatusLabel1.Text = "Cancelled";
                        _synchronizationContext.Post(_ => ResetForm(), null);
                    }
                }, _cancellationTokenSource.Token);

                taskList.Add(task);
            }
            reader.Close();

            Task.WhenAll(taskList).ContinueWith(task =>
                                                    {
                                                        if (concurrentBag.Count != 0)
                                                        {
                                                            File.WriteAllLines(
                                                                Path.Combine(logPath, "SqlMassDumper.log"),
                                                                concurrentBag.ToArray());
                                                        }
                                                        MessageBox.Show("Done");
                                                        toolStripStatusLabel1.Text = "Done...";
                                                    });

        }

        private void BtnCancelClick(object sender, EventArgs e)
        {
            _cancellationTokenSource.Cancel();
        }

    }
}
